﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using KCL.Code.Entities;

namespace KCL.Code.Data
{
	public class CustomerDAO : BaseDAO
	{
		public List<Customer> GetCustomers(int maximumRows, int startRowIndex, string sortExpression)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("WITH OrderedCustomer AS (SELECT *, ROW_NUMBER() OVER (ORDER BY {0}) AS 'RowNumber' FROM Customer WHERE isActive=1) SELECT * FROM OrderedCustomer WHERE RowNumber BETWEEN {1} AND {2}", sortExpression != string.Empty ? sortExpression : "id", startRowIndex, startRowIndex + maximumRows);
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
			myConnection.Open();
			DataTable dt = new DataTable();
			myAdapter.Fill(dt);
			List<Customer> results = new List<Customer>();
			int currentIndex = 0;
			while (currentIndex < dt.Rows.Count)
			{
				Customer customer = new Customer();
				customer.id = Convert.ToInt32(dt.Rows[currentIndex]["id"]);
				customer.name = dt.Rows[currentIndex]["name"].ToString();
				customer.address = dt.Rows[currentIndex]["address"].ToString();
				customer.contactAddress = dt.Rows[currentIndex]["contactAddress"].ToString();
				customer.contactPerson = dt.Rows[currentIndex]["contactPerson"].ToString();
				customer.phone = dt.Rows[currentIndex]["phone"].ToString();
				customer.mobile = dt.Rows[currentIndex]["mobile"].ToString();
				customer.fax = dt.Rows[currentIndex]["fax"].ToString();
				customer.accountNumber = dt.Rows[currentIndex]["accountNumber"].ToString();
				customer.bank = dt.Rows[currentIndex]["bank"].ToString();
				customer.taxNumber = dt.Rows[currentIndex]["taxNumber"].ToString();
				customer.director = dt.Rows[currentIndex]["director"].ToString();
				customer.code = dt.Rows[currentIndex]["code"].ToString();
				results.Add(customer);
				currentIndex++;
			}
			myConnection.Close();
			return results;
		}
        public Customer GetCustomer(int customerID)
        {
            SqlConnection myConnection = this.GetConnection();
            SqlCommand myCommand = new SqlCommand(string.Format("SELECT * FROM Customer WHERE ID = {0}", customerID), myConnection);
            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
            myConnection.Open();
            DataTable dt = new DataTable();
            myAdapter.Fill(dt);
            if (dt.Rows.Count == 0) return null;
            Customer customer = new Customer();
            customer.id = Convert.ToInt32(dt.Rows[0]["id"]);
            customer.name = dt.Rows[0]["name"].ToString();
            customer.address = dt.Rows[0]["address"].ToString();
            customer.contactAddress = dt.Rows[0]["contactAddress"].ToString();
            customer.contactPerson = dt.Rows[0]["contactPerson"].ToString();
            customer.phone = dt.Rows[0]["phone"].ToString();
            customer.mobile = dt.Rows[0]["mobile"].ToString();
            customer.fax = dt.Rows[0]["fax"].ToString();
            customer.accountNumber = dt.Rows[0]["accountNumber"].ToString();
            customer.bank = dt.Rows[0]["bank"].ToString();
            customer.taxNumber = dt.Rows[0]["taxNumber"].ToString();
            customer.director = dt.Rows[0]["director"].ToString();
            customer.code = dt.Rows[0]["code"].ToString();
            myConnection.Close();
            return customer;
        }
		public int GetTotalCustomer()
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = "SELECT COUNT(*) FROM Customer WHERE isActive=1";
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			myConnection.Open();
			int totalCustomer = Convert.ToInt32(myCommand.ExecuteScalar());
			myConnection.Close();
			return totalCustomer;
		}

		public string CreateCustomer(string name, string address, string contactAddress, string contactPerson, string phone, string mobile, string fax, string accountNumber, string bank, string taxNumber, string director)
		{
			SqlConnection myConnection = this.GetConnection();
			string code = "";
			string sqlStatement = string.Format("INSERT INTO [Customer]([name], [address], [contactAddress], [contactPerson], [phone], [mobile], [fax], [accountNumber], [bank], [taxNumber], [director], [code]) VALUES(N'{0}', N'{1}', N'{2}', N'{3}', N'{4}', N'{5}', N'{6}', N'{7}', N'{8}', N'{9}', N'{10}', N'{11}')", name, address, contactAddress, contactPerson, phone, mobile, fax, accountNumber, bank, taxNumber, director, code);
			sqlStatement = sqlStatement.Replace("N''", "NULL");
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myCommand.CommandText = "SELECT @@IDENTITY";
				object customerId = myCommand.ExecuteScalar();
				code = "KH" + customerId.ToString();
				myCommand.CommandText = string.Format("UPDATE [Customer] SET [code]=N'{0}' WHERE [id]={1}", code, customerId);
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return code;
		}

		public bool UpdateCustomer(string id, string name, string address, string contactAddress, string contactPerson, string phone, string mobile, string fax, string accountNumber, string bank, string taxNumber, string director)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("UPDATE [Customer] SET name=N'{0}', address=N'{1}', contactAddress=N'{2}', contactPerson=N'{3}', phone=N'{4}', mobile=N'{5}', fax=N'{6}', accountNumber=N'{7}', bank=N'{8}', taxNumber=N'{9}', director=N'{10}' WHERE id={11}", name, address, contactAddress, contactPerson, phone, mobile, fax, accountNumber, bank, taxNumber, director, id);
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return true;
		}

		public bool DeleteCustomer(string id)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("UPDATE [Customer] SET isActive=0 WHERE id={0}", id);
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return true;
		}
		public List<Customer> SearchCustomer(string name)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("SELECT TOP 10 * FROM Customer WHERE isActive=1 AND name LIKE '%{0}%'", name);
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
			myConnection.Open();
			DataTable dt = new DataTable();
			myAdapter.Fill(dt);
			List<Customer> results = new List<Customer>();
			int currentIndex = 0;
			while (currentIndex < dt.Rows.Count)
			{
				Customer customer = new Customer();
				customer.id = Convert.ToInt32(dt.Rows[currentIndex]["id"]);
				customer.name = dt.Rows[currentIndex]["name"].ToString();
				customer.address = dt.Rows[currentIndex]["address"].ToString();
				customer.contactAddress = dt.Rows[currentIndex]["contactAddress"].ToString();
				customer.contactPerson = dt.Rows[currentIndex]["contactPerson"].ToString();
				customer.phone = dt.Rows[currentIndex]["phone"].ToString();
				customer.mobile = dt.Rows[currentIndex]["mobile"].ToString();
				customer.fax = dt.Rows[currentIndex]["fax"].ToString();
				customer.accountNumber = dt.Rows[currentIndex]["accountNumber"].ToString();
				customer.bank = dt.Rows[currentIndex]["bank"].ToString();
				customer.taxNumber = dt.Rows[currentIndex]["taxNumber"].ToString();
				customer.director = dt.Rows[currentIndex]["director"].ToString();
				customer.code = dt.Rows[currentIndex]["code"].ToString();
				results.Add(customer);
				currentIndex++;
			}
			myConnection.Close();
			return results;
		}
	}
}